# Load data
zip_cbsa_data <- read_csv(url('https://raw.githubusercontent.com/cyouh95/third-way-report/master/assets/data/zip_code_cbsa.csv'))
hs_data <- read_csv(url('https://github.com/cyouh95/third-way-report/blob/master/assets/data/hs_data.csv?raw=true'), col_types = c('zip_code' = 'c'))
ceeb_nces <- read_csv(url('https://github.com/mpatricia01/public_requests_eda/raw/main/data/ceeb_nces_crosswalk.csv'))
cds_nces <- read_csv(url('https://github.com/mpatricia01/public_requests_eda/raw/main/data/CDS_NCES_crosswalk.csv')) %>%
mutate(ncessch = str_c(NCESDist, NCESSchool))
load(url('https://github.com/mpatricia01/public_requests_eda/raw/main/data/145637_orders.RData'))
# Contains: IL_orders, OOS_orders, OOS_eng_orders, OOS_noneng_orders, intl_orders,
# lists_df_pivot, lists_df_sat, lists_df_act, df_sat_ca_20, df_sat_ca_19
# Add 11 + 12 columns for the SAT test takers datasets
add_testtakers_cols <- function(sat_df) {
sat_df %>% mutate(
Enroll1112 = as.numeric(Enroll12) + as.numeric(Enroll11),
NumTSTTakr1112 = NumTSTTakr11 + NumTSTTakr12,
NumERWBenchmark1112 = as.numeric(NumERWBenchmark11) + as.numeric(NumERWBenchmark12),
PctERWBenchmark1112 = as.numeric(PctERWBenchmark11) + as.numeric(PctERWBenchmark12),
NumMathBenchmark1112 = as.numeric(NumMathBenchmark11) + as.numeric(NumMathBenchmark12),
PctMathBenchmark1112 = as.numeric(PctMathBenchmark11) + as.numeric(PctMathBenchmark12),
TotNumBothBenchmark1112 = as.numeric(TotNumBothBenchmark11) + as.numeric(TotNumBothBenchmark12),
PctBothBenchmark1112 = as.numeric(PctBothBenchmark11) + as.numeric(PctBothBenchmark12)
)
}
df_sat_ca_20 <- add_testtakers_cols(df_sat_ca_20)
df_sat_ca_19 <- add_testtakers_cols(df_sat_ca_19)IL_orders## # A tibble: 51 x 25
## univ_id po_num order_num order_title order_cost num_runs num_students date_start date_end hs_grad_class state_name cbsa_name race_ethnicity sat_score_min sat_score_max sat_score_old_min sat_score_old_max psat_score_min psat_score_max psat_score_old_min psat_score_old_max gpa_high gpa_low rank_high rank_low
## <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <date> <date> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <chr> <chr>
## 1 145637 P1558959 327699 IL Lower Range August 2017 4611. NA 10723 2017-10-12 NA 2018|2019|2020 Illinois NA Asian (including Indian subcontinent and Philippines origin)|Other|I do not wish to respond to race|No, not of Hispanic, Latino, or Spanish origin|White (including Middle Eastern origin)|Native Hawaiian or Other Pacific Islander 1160 1270 1090 1200 1160 1270 109 120 A+ B- Highest tenth Second fifth
## 2 145637 P1558959 327649 IL Middle 50% August 2017 1762. NA 4097 2017-10-12 NA 2018|2019|2020 Illinois NA Asian (including Indian subcontinent and Philippines origin)|Other|I do not wish to respond to race|No, not of Hispanic, Latino, or Spanish origin|White (including Middle Eastern origin)|Native Hawaiian or Other Pacific Islander 1280 1440 1210 1390 1280 1440 121 139 A+ B- Highest tenth Second fifth
## 3 145637 P1558959 327696 IL 1450+ August 2017 241. NA 561 2017-10-12 NA 2018|2019|2020 Illinois NA Asian (including Indian subcontinent and Philippines origin)|Other|I do not wish to respond to race|No, not of Hispanic, Latino, or Spanish origin|White (including Middle Eastern origin)|Native Hawaiian or Other Pacific Islander 1450 1600 1400 1600 1450 1520 140 149 A+ B- Highest tenth Second fifth
## 4 145637 P1558959 327681 URM Lower Range August 2017 2859. NA 6648 2017-10-12 NA 2018|2019|2020 Illinois NA Black or African American|American Indian or Alaska Native|Other Hispanic or Latino|Puerto Rican|Mexican|Hispanic or Latino (including Spanish origin)|Cuban 1020 1190 940 1120 1020 1190 94 112 A+ B- Highest tenth Second fifth
## 5 145637 P1558959 327700 PAP Traditional August 2017 110. NA 255 2017-10-12 NA 2018|2019|2020 Illinois NA Black or African American|American Indian or Alaska Native|Other Hispanic or Latino|Puerto Rican|Mexican|Hispanic or Latino (including Spanish origin)|Cuban 1200 1380 1130 1320 1200 1380 113 132 A+ B- Highest tenth Second fifth
## 6 145637 P1558959 327630 PAP Honors August 2017 11.6 NA 27 2017-10-12 NA 2018|2019|2020 Illinois NA Black or African American|American Indian or Alaska Native|Other Hispanic or Latino|Puerto Rican|Mexican|Hispanic or Latino (including Spanish origin)|Cuban 1390 1600 1330 1600 1390 1520 133 149 A+ B- Highest tenth Second fifth
## 7 145637 P1558959 371076 IL 1160-1270 Feb 2018 4849. NA 11277 2018-02-06 NA 2019|2021|2020 Illinois NA Asian (including Indian subcontinent and Philippines origin)|Other|I do not wish to respond to race|No, not of Hispanic, Latino, or Spanish origin|White (including Middle Eastern origin)|Native Hawaiian or Other Pacific Islander 1160 1270 1090 1200 1160 1270 109 120 A+ B- Highest tenth Second fifth
## 8 145637 P1558959 371072 IL 1280-1440 Feb 2018 1819. NA 4230 2018-02-06 NA 2019|2021|2020 Illinois NA Asian (including Indian subcontinent and Philippines origin)|Other|I do not wish to respond to race|No, not of Hispanic, Latino, or Spanish origin|White (including Middle Eastern origin)|Native Hawaiian or Other Pacific Islander 1280 1440 1210 1390 1280 1440 121 139 A+ B- Highest tenth Second fifth
## 9 145637 P1558959 371132 IL 1450+ Feb 2018 218. NA 506 2018-02-06 NA 2019|2021|2020 Illinois NA Asian (including Indian subcontinent and Philippines origin)|Other|I do not wish to respond to race|No, not of Hispanic, Latino, or Spanish origin|White (including Middle Eastern origin)|Native Hawaiian or Other Pacific Islander 1450 1600 1400 1600 1450 1520 140 149 A+ B- Highest tenth Second fifth
## 10 145637 P1558959 371079 URM 1020-1190 Feb 2018 2669. NA 6207 2018-02-06 NA 2019|2021|2020 Illinois NA Black or African American|American Indian or Alaska Native|Other Hispanic or Latino|Puerto Rican|Mexican|Hispanic or Latino (including Spanish origin)|Cuban 1020 1190 940 1120 1020 1190 94 112 A+ B- Highest tenth Second fifth
## # … with 41 more rows
Generally, on each purchase date, they make 6 IL orders by race/ethnicity and test scores:
Group/filter definitions:
Common filters:
Based on geographic filters, we can categorize their orders into 3 broad categories:
21 of the 22 out-of-state orders also use these segment analysis filters (total possible: 33 neighborhood clusters and 29 high-school clusters):
Sample neighborhood clusters (EN) characteristics from 2011:
Sample high school clusters (HS) characteristics from 2011:
OOS_msa_orders <- OOS_orders %>% filter(order_num %in% c('500590', '567376', '483751'))
OOS_msa_orders## # A tibble: 3 x 23
## univ_id po_num order_num order_title order_cost num_runs num_students date_start hs_grad_class state_name cbsa_name segment gender sat_score_min sat_score_max sat_score_old_min sat_score_old_max psat_score_min psat_score_max psat_score_old_min psat_score_old_max gpa_high gpa_low
## <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <date> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr>
## 1 145637 P1887010 500590 OOS Regional MSAs 11273. 4 23985 2019-09-04 2021|2020|2023|2022 NA NY - Syracuse, NY|FL - Orlando-Kissimmee-Sanford, FL|FL - Sebring, FL|GA - Warner Robins, GA|GA - Dalton, GA|CA - Bakersfield, CA|TX - San Angelo, TX|TX - College Station-Bryan, TX|GA - Valdosta, GA|FL - Palm Bay-Melbourne-Titusville, FL|GA - Columbus, GA-AL|FL - Port St. Lucie, FL|GA - Savannah, GA|CA - Visalia-Porterville, CA|NY - Rochester, NY|CA - Santa Rosa, CA|CA - Chico, CA|GA - Macon-Bibb County, GA|FL - Tampa-St. Petersburg-Clearwater, FL|GA - Chattanooga, TN-GA|GA - Brunswick, GA|FL - Gainesville, FL|TX - Midland, TX|FL - Cape Coral-Fort Myers, FL|FL - Crestview-Fort Walton Beach-Destin, FL|FL - Punta Gorda, FL|CA - San Diego-Carlsbad, CA|CA - Oxnard-Thousand Oaks-Ventura, CA|TX - Beaumont-Port Arthur, TX|GA - Albany, GA|GA - Hinesville, GA|FL - North Port-Sarasota-Bradenton, FL|FL - Jacksonville, FL|TX - San Antonio-New Braunfels, TX|TX - Killeen-Temple, TX|TX - Abilene, TX|GA - Atlanta-Sandy Springs-Roswell, GA|NJ - New York-Newark-Jersey City, NY-NJ-PA|FL - Ocala, FL|NJ - Vineland-Bridgeton, NJ|FL - Deltona-Daytona Beach-Ormond Beach, FL|TX - Waco, TX|NY - Buffalo-Cheektowaga-Niagara Falls, NY|GA - Augusta-Richmond County, GA-SC|TX - Sherman-Denison, TX|NY - Glens Falls, NY|FL - Lakeland-Winter Haven, FL|CA - San Luis Obispo-Paso Robles-Arroyo Grande, CA|CA - Vallejo-Fairfield, CA|TX - Texarkana, TX-AR|CA - San Francisco-Oakland-Hayward, CA|TX - Laredo, TX|FL - Homosassa Springs, FL|CA - Stockton-Lodi, CA|CA - Modesto, CA|TX - Odessa, TX|TX - Lubbock, TX|NY - Albany-Schenectady-Troy, NY|CA - Riverside-San Bernardino-Ontario, CA|GA - Gainesville, GA|TX - Amarillo, TX|GA - Rome, GA|CA - Sacramento--Roseville--Arden-Arcade, CA|FL - Tallahassee, FL|FL - Miami-Fort Lauderdale-West Palm Beach, FL|FL - Naples-Immokalee-Marco Island, FL|NJ - Philadelphia-Camden-Wilmington, PA-NJ-DE-MD|FL - Sebastian-Vero Beach, FL|CA - Santa Cruz-Watsonville, CA|NY - Watertown-Fort Drum, NY|CA - San Jose-Sunnyvale-Santa Clara, CA|CA - Madera, CA|NY - New York-Newark-Jersey City, NY-NJ-PA|TX - Brownsville-Harlingen, TX|FL - The Villages, FL|CA - El Centro, CA|NJ - Trenton, NJ|TX - Corpus Christi, TX|NJ - Atlantic City-Hammonton, NJ|NY - Ithaca, NY|NY - Binghamton, NY|TX - El Paso, TX|TX - McAllen-Edinburg-Mission, TX|FL - Panama City, FL|TX - Houston-The Woodlands-Sugar Land, TX|CA - Santa Maria-Santa Barbara, CA|CA - Hanford-Corcoran, CA|CA - Salinas, CA|CA - Yuba City, CA|CA - Fresno, CA|NJ - Ocean City, NJ|FL - Pensacola-Ferry Pass-Brent, FL|CA - Los Angeles-Long Beach-Anaheim, CA|TX - Dallas-Fort Worth-Arlington, TX|TX - Wichita Falls, TX|NY - Elmira, NY|CA - Redding, CA|TX - Longview, TX|TX - Austin-Round Rock, TX|NY - Utica-Rome, NY|CA - Napa, CA|TX - Tyler, TX|CA - Merced, CA|TX - Victoria, TX|NY - Kingston, NY|GA - Athens-Clarke County, GA|NJ - Allentown-Bethlehem-Easton, PA-NJ EN:51, HS:68|EN:58, HS:70|EN:53, HS:70|EN:51, HS:65|EN:69, HS:70|EN:60, HS:68|EN:73, HS:70|EN:60, HS:65|EN:69, HS:68|EN:70, HS:68|EN:63, HS:70|EN:70, HS:66|EN:78, HS:ALL|EN:58, HS:65|EN:58, HS:64|EN:51, HS:79|EN:60, HS:70|EN:51, HS:70|EN:53, HS:65|EN:60, HS:79|EN:69, HS:75|EN:70, HS:70|EN:63, HS:65|EN:61, HS:ALL|EN:73, HS:65|EN:70, HS:79 NA 1240 1450 1170 1400 1220 1450 115 140 A+ B-
## 2 145637 P1887010 567376 OOS Regional MSAs 10980. NA 23361 2020-04-03 2021|2023|2022 NA NY - Syracuse, NY|FL - Orlando-Kissimmee-Sanford, FL|FL - Sebring, FL|GA - Warner Robins, GA|GA - Dalton, GA|CA - Bakersfield, CA|TX - San Angelo, TX|TX - College Station-Bryan, TX|GA - Valdosta, GA|FL - Palm Bay-Melbourne-Titusville, FL|GA - Columbus, GA-AL|FL - Port St. Lucie, FL|GA - Savannah, GA|CA - Visalia-Porterville, CA|NY - Rochester, NY|CA - Santa Rosa, CA|CA - Chico, CA|GA - Macon-Bibb County, GA|FL - Tampa-St. Petersburg-Clearwater, FL|GA - Chattanooga, TN-GA|GA - Brunswick, GA|FL - Gainesville, FL|TX - Midland, TX|FL - Cape Coral-Fort Myers, FL|FL - Crestview-Fort Walton Beach-Destin, FL|FL - Punta Gorda, FL|CA - San Diego-Carlsbad, CA|CA - Oxnard-Thousand Oaks-Ventura, CA|TX - Beaumont-Port Arthur, TX|GA - Albany, GA|GA - Hinesville, GA|FL - North Port-Sarasota-Bradenton, FL|FL - Jacksonville, FL|TX - San Antonio-New Braunfels, TX|TX - Killeen-Temple, TX|TX - Abilene, TX|GA - Atlanta-Sandy Springs-Roswell, GA|NJ - New York-Newark-Jersey City, NY-NJ-PA|FL - Ocala, FL|NJ - Vineland-Bridgeton, NJ|FL - Deltona-Daytona Beach-Ormond Beach, FL|TX - Waco, TX|NY - Buffalo-Cheektowaga-Niagara Falls, NY|GA - Augusta-Richmond County, GA-SC|TX - Sherman-Denison, TX|NY - Glens Falls, NY|FL - Lakeland-Winter Haven, FL|CA - San Luis Obispo-Paso Robles-Arroyo Grande, CA|CA - Vallejo-Fairfield, CA|TX - Texarkana, TX-AR|CA - San Francisco-Oakland-Hayward, CA|TX - Laredo, TX|FL - Homosassa Springs, FL|CA - Stockton-Lodi, CA|CA - Modesto, CA|TX - Odessa, TX|TX - Lubbock, TX|NY - Albany-Schenectady-Troy, NY|CA - Riverside-San Bernardino-Ontario, CA|GA - Gainesville, GA|TX - Amarillo, TX|GA - Rome, GA|CA - Sacramento--Roseville--Arden-Arcade, CA|FL - Tallahassee, FL|FL - Miami-Fort Lauderdale-West Palm Beach, FL|FL - Naples-Immokalee-Marco Island, FL|NJ - Philadelphia-Camden-Wilmington, PA-NJ-DE-MD|FL - Sebastian-Vero Beach, FL|CA - Santa Cruz-Watsonville, CA|NY - Watertown-Fort Drum, NY|CA - San Jose-Sunnyvale-Santa Clara, CA|CA - Madera, CA|NY - New York-Newark-Jersey City, NY-NJ-PA|TX - Brownsville-Harlingen, TX|FL - The Villages, FL|CA - El Centro, CA|NJ - Trenton, NJ|TX - Corpus Christi, TX|NJ - Atlantic City-Hammonton, NJ|NY - Ithaca, NY|NY - Binghamton, NY|TX - El Paso, TX|TX - McAllen-Edinburg-Mission, TX|FL - Panama City, FL|TX - Houston-The Woodlands-Sugar Land, TX|CA - Santa Maria-Santa Barbara, CA|CA - Hanford-Corcoran, CA|CA - Salinas, CA|CA - Yuba City, CA|CA - Fresno, CA|NJ - Ocean City, NJ|FL - Pensacola-Ferry Pass-Brent, FL|CA - Los Angeles-Long Beach-Anaheim, CA|TX - Dallas-Fort Worth-Arlington, TX|TX - Wichita Falls, TX|NY - Elmira, NY|CA - Redding, CA|TX - Longview, TX|TX - Austin-Round Rock, TX|NY - Utica-Rome, NY|CA - Napa, CA|TX - Tyler, TX|CA - Merced, CA|TX - Victoria, TX|NY - Kingston, NY|GA - Athens-Clarke County, GA|NJ - Allentown-Bethlehem-Easton, PA-NJ EN:51, HS:68|EN:58, HS:70|EN:53, HS:70|EN:51, HS:65|EN:69, HS:70|EN:60, HS:68|EN:73, HS:70|EN:60, HS:65|EN:69, HS:68|EN:70, HS:68|EN:63, HS:70|EN:70, HS:66|EN:78, HS:ALL|EN:58, HS:65|EN:58, HS:64|EN:51, HS:79|EN:60, HS:70|EN:51, HS:70|EN:53, HS:65|EN:60, HS:79|EN:69, HS:75|EN:70, HS:70|EN:63, HS:65|EN:61, HS:ALL|EN:73, HS:65|EN:70, HS:79 NA 1240 1450 1170 1400 1220 1450 115 140 A+ B-
## 3 145637 P1659111 483751 Regional Counselor MSAs 674. NA 1497 2019-06-27 2021|2022 NA FL - Orlando-Kissimmee-Sanford, FL|FL - Sebring, FL|GA - Warner Robins, GA|GA - Dalton, GA|CA - Bakersfield, CA|TX - San Angelo, TX|TX - College Station-Bryan, TX|GA - Valdosta, GA|FL - Palm Bay-Melbourne-Titusville, FL|GA - Columbus, GA-AL|FL - Port St. Lucie, FL|GA - Savannah, GA|CA - Visalia-Porterville, CA|CA - Santa Rosa, CA|CA - Chico, CA|GA - Macon-Bibb County, GA|FL - Tampa-St. Petersburg-Clearwater, FL|GA - Chattanooga, TN-GA|GA - Brunswick, GA|FL - Gainesville, FL|TX - Midland, TX|FL - Cape Coral-Fort Myers, FL|FL - Crestview-Fort Walton Beach-Destin, FL|FL - Punta Gorda, FL|CA - San Diego-Carlsbad, CA|CA - Oxnard-Thousand Oaks-Ventura, CA|TX - Beaumont-Port Arthur, TX|GA - Albany, GA|GA - Hinesville, GA|FL - North Port-Sarasota-Bradenton, FL|FL - Jacksonville, FL|TX - San Antonio-New Braunfels, TX|TX - Killeen-Temple, TX|TX - Abilene, TX|GA - Atlanta-Sandy Springs-Roswell, GA|NJ - New York-Newark-Jersey City, NY-NJ-PA|FL - Ocala, FL|NJ - Vineland-Bridgeton, NJ|FL - Deltona-Daytona Beach-Ormond Beach, FL|TX - Waco, TX|GA - Augusta-Richmond County, GA-SC|TX - Sherman-Denison, TX|FL - Lakeland-Winter Haven, FL|CA - San Luis Obispo-Paso Robles-Arroyo Grande, CA|CA - Vallejo-Fairfield, CA|TX - Texarkana, TX-AR|CA - San Francisco-Oakland-Hayward, CA|TX - Laredo, TX|FL - Homosassa Springs, FL|CA - Stockton-Lodi, CA|CA - Modesto, CA|TX - Odessa, TX|TX - Lubbock, TX|CA - Riverside-San Bernardino-Ontario, CA|GA - Gainesville, GA|TX - Amarillo, TX|GA - Rome, GA|CA - Sacramento--Roseville--Arden-Arcade, CA|FL - Tallahassee, FL|FL - Miami-Fort Lauderdale-West Palm Beach, FL|FL - Naples-Immokalee-Marco Island, FL|NJ - Philadelphia-Camden-Wilmington, PA-NJ-DE-MD|FL - Sebastian-Vero Beach, FL|CA - Santa Cruz-Watsonville, CA|CA - San Jose-Sunnyvale-Santa Clara, CA|CA - Madera, CA|NY - New York-Newark-Jersey City, NY-NJ-PA|TX - Brownsville-Harlingen, TX|FL - The Villages, FL|CA - El Centro, CA|NJ - Trenton, NJ|TX - Corpus Christi, TX|NJ - Atlantic City-Hammonton, NJ|TX - El Paso, TX|TX - McAllen-Edinburg-Mission, TX|FL - Panama City, FL|TX - Houston-The Woodlands-Sugar Land, TX|CA - Santa Maria-Santa Barbara, CA|CA - Hanford-Corcoran, CA|CA - Salinas, CA|CA - Yuba City, CA|CA - Fresno, CA|NJ - Ocean City, NJ|FL - Pensacola-Ferry Pass-Brent, FL|CA - Los Angeles-Long Beach-Anaheim, CA|TX - Dallas-Fort Worth-Arlington, TX|TX - Wichita Falls, TX|CA - Redding, CA|TX - Longview, TX|TX - Austin-Round Rock, TX|CA - Napa, CA|TX - Tyler, TX|CA - Merced, CA|TX - Victoria, TX|GA - Athens-Clarke County, GA|NJ - Allentown-Bethlehem-Easton, PA-NJ EN:51, HS:68|EN:58, HS:70|EN:53, HS:70|EN:51, HS:65|EN:69, HS:70|EN:60, HS:68|EN:73, HS:70|EN:60, HS:65|EN:69, HS:68|EN:70, HS:68|EN:63, HS:70|EN:70, HS:66|EN:78, HS:ALL|EN:58, HS:65|EN:58, HS:64|EN:51, HS:79|EN:60, HS:70|EN:51, HS:70|EN:53, HS:65|EN:60, HS:79|EN:69, HS:75|EN:70, HS:70|EN:63, HS:65|EN:61, HS:ALL|EN:73, HS:65|EN:70, HS:79 NA 1240 1450 1170 1400 1240 1450 117 140 A+ B-
They made 2 “OOS Regional MSA” orders 1 “Regional Counselor MSAs” order.